package com.evaluation.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * Excel工具类
 */
public class ExcelUtil {

    /**
     * 读取Excel文件
     *
     * @param file  Excel文件
     * @param clazz 实体类
     * @param <T>   泛型
     * @return 数据列表
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz) throws Exception {
        List<T> list = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 获取表头
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new Exception("Excel文件格式错误：缺少表头");
        }

        Map<String, Integer> headerMap = new HashMap<>();
        for (Cell cell : headerRow) {
            headerMap.put(cell.getStringCellValue(), cell.getColumnIndex());
        }

        // 获取实体类字段信息
        Field[] fields = clazz.getDeclaredFields();
        Map<String, Field> fieldMap = new HashMap<>();
        for (Field field : fields) {
            field.setAccessible(true);
            fieldMap.put(field.getName(), field);
        }

        // 读取数据行
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            T instance = clazz.newInstance();

            // 根据表头映射字段
            for (Map.Entry<String, Integer> entry : headerMap.entrySet()) {
                String header = entry.getKey();
                Integer columnIndex = entry.getValue();

                Cell cell = row.getCell(columnIndex);
                if (cell == null) continue;

                String fieldName = getFieldNameByHeader(header);
                Field field = fieldMap.get(fieldName);
                if (field != null) {
                    setFieldValue(instance, field, getCellValue(cell));
                }
            }

            list.add(instance);
        }

        workbook.close();
        inputStream.close();

        return list;
    }

    /**
     * 导出Excel文件
     *
     * @param response HTTP响应
     * @param data     数据列表
     * @param headers  表头
     * @param fileName 文件名
     * @param <T>      泛型
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> data,
                                      String[] headers, String[] fields, String fileName) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("数据");

        // 创建表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        // 创建表头
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

        // 创建数据行
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i + 1);
            T item = data.get(i);

            for (int j = 0; j < fields.length; j++) {
                Cell cell = row.createCell(j);
                try {
                    Field field = item.getClass().getDeclaredField(fields[j]);
                    field.setAccessible(true);
                    Object value = field.get(item);
                    cell.setCellValue(value != null ? value.toString() : "");
                } catch (Exception e) {
                    cell.setCellValue("");
                }
            }
        }

        // 自适应列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    /**
     * 导出Excel文件（Map数据版本）
     *
     * @param response HTTP响应
     * @param fileName 文件名
     * @param data     Map数据列表
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List<Map<String, Object>> data) throws IOException {
        exportExcel(response, fileName, data, null);
    }

    /**
     * 导出Excel文件（Map数据版本，支持指定表头顺序）
     *
     * @param response HTTP响应
     * @param fileName 文件名
     * @param data     Map数据列表
     * @param headers  表头顺序（如果为null，则使用Map的key顺序）
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List<Map<String, Object>> data, List<String> headers) throws IOException {
        if (data == null || data.isEmpty()) {
            throw new IllegalArgumentException("导出数据不能为空");
        }

        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        String encodedFileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + encodedFileName);

        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(fileName);

        // 获取表头
        String[] headerArray;
        if (headers != null && !headers.isEmpty()) {
            // 使用指定的表头顺序
            headerArray = headers.toArray(new String[0]);
        } else {
            // 从第一行数据的键集合获取表头（保持LinkedHashMap的顺序）
            Set<String> keySet = data.get(0).keySet();
            headerArray = keySet.toArray(new String[0]);
        }

        // 创建表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        // 创建表头
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headerArray.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headerArray[i]);
            cell.setCellStyle(headerStyle);
        }

        // 创建数据行
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Map<String, Object> item = data.get(i);

            for (int j = 0; j < headerArray.length; j++) {
                Cell cell = row.createCell(j);
                Object value = item.get(headerArray[j]);

                if (value != null) {
                    if (value instanceof Number) {
                        cell.setCellValue(((Number) value).doubleValue());
                    } else {
                        cell.setCellValue(value.toString());
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }

        // 自适应列宽
        for (int i = 0; i < headerArray.length; i++) {
            sheet.autoSizeColumn(i);
        }

        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    /**
     * 读取Excel文件返回Map数据
     *
     * @param file Excel文件
     * @return Map数据列表
     */
    public static List<Map<String, Object>> readExcel(MultipartFile file) throws Exception {
        List<Map<String, Object>> list = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 获取表头
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new Exception("Excel文件格式错误：缺少表头");
        }

        List<String> headers = new ArrayList<>();
        for (Cell cell : headerRow) {
            headers.add(cell.getStringCellValue());
        }

        // 读取数据行
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            Map<String, Object> data = new HashMap<>();
            for (int j = 0; j < headers.size(); j++) {
                Cell cell = row.getCell(j);
                String header = headers.get(j);
                String value = getCellValue(cell);
                data.put(header, value);
            }
            
            // 只添加非空行
            if (!data.values().stream().allMatch(v -> v == null || v.toString().trim().isEmpty())) {
                list.add(data);
            }
        }

        workbook.close();
        inputStream.close();
        return list;
    }

    /**
     * 根据表头获取字段名
     */
    private static String getFieldNameByHeader(String header) {
        Map<String, String> mapping = new HashMap<>();
        // 部门相关字段
        mapping.put("部门编号", "bmbh");
        mapping.put("部门名称", "bmmc");
        mapping.put("部门类型", "bmlx");
        mapping.put("部门状态", "bmzt");
        // 岗位相关字段
        mapping.put("岗位编号", "gwbh");
        mapping.put("岗位名称", "gwmc");
        mapping.put("岗位类型", "gwlx");
        mapping.put("状态", "gwzt");
        // 教师相关字段
        mapping.put("教师编号", "jsbh");
        mapping.put("教师姓名", "jsxm");
        mapping.put("教师类型", "jslx");
        mapping.put("是否参评", "sfcp");
        mapping.put("手机号码", "sjhm");
        mapping.put("教师状态", "jszt");
        // 考核相关字段
        mapping.put("考核指标编号", "zbbh");
        mapping.put("考核指标名称", "zbmc");
        mapping.put("排序", "px");
        mapping.put("考核方式", "khfs");
        mapping.put("考核点编号", "khdbh");
        mapping.put("考核点名称", "khdmc");
        mapping.put("考核点详解", "khdxj");
        mapping.put("考核点权重", "khdqz");
        mapping.put("A等分值", "a");
        mapping.put("B等分值", "b");
        mapping.put("C等分值", "c");
        mapping.put("D等分值", "d");
        mapping.put("E等分值", "e");
        // 指标权重相关字段
        mapping.put("权重编号", "qzbh");
        mapping.put("权重名称", "qzmc");
        mapping.put("权重", "qz");
        mapping.put("测评类型", "cplx");

        return mapping.getOrDefault(header, header);
    }

    /**
     * 获取单元格值
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf((long) cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    /**
     * 设置字段值
     */
    private static void setFieldValue(Object instance, Field field, String value) throws IllegalAccessException {
        if (value == null || value.trim().isEmpty()) return;

        Class<?> fieldType = field.getType();
        if (fieldType == String.class) {
            field.set(instance, value);
        } else if (fieldType == Integer.class || fieldType == int.class) {
            field.set(instance, Integer.parseInt(value));
        } else if (fieldType == Long.class || fieldType == long.class) {
            field.set(instance, Long.parseLong(value));
        } else if (fieldType == Double.class || fieldType == double.class) {
            field.set(instance, Double.parseDouble(value));
        }
    }
}
